﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Core.Entity;
using System.Data;

namespace Core.Dao
{
    class TicketDao
    {
        public bool insert(Ticket ticket)
        {
            object[,] param = {
                {"@ServiceTypeId", ticket.ServiceTypeId},
                {"@DepartmentId", ticket.DepartmentId},
                {"@OrderLineId", ticket.OrderLineId},
                {"@TicketCategoryId", ticket.TicketCategoryId},
                {"@StaffId", ticket.StaffId},
                {"@TicketStatusId", ticket.TicketStatusId},
                {"@Number", ticket.Number},
                {"@Subject", ticket.Subject},
                {"@ServicePassword", ticket.ServicePassword},
                {"@Content", ticket.Content},
                {"@Attachment", ticket.Attachment},
                {"@ParentId", ticket.ParentId},
                {"@ParentType", ticket.ParentType},
            };
            String sql = "insert into TICKETS(SVT_ID, DEP_ID, OLN_ID, TKC_ID, STF_ID, TKS_ID, TKT_NUMBER, TKT_SUBJECT, TKT_SERVICE_PASSWORD, TKT_CONTENT, TKT_ATTACHMENT, PARENT_ID, PARENT_TYPE, TKT_TIMESTAMP) " +
                "values(@ServiceTypeId, @DepartmentId, @OrderLineId, @TicketCategoryId, @StaffId, @TicketStatusId, @Number, @Subject, @ServicePassword, @Content, @Attachment, @ParentId, @ParentType, GETDATE())";
            return DB.exec(sql, param);
        }

        public bool update(Ticket ticket)
        {
            object[,] param = {
                {"@ServiceTypeId", ticket.ServiceTypeId},
                {"@DepartmentId", ticket.DepartmentId},
                {"@OrderLineId", ticket.OrderLineId},
                {"@TicketCategoryId", ticket.TicketCategoryId},
                {"@StaffId", ticket.StaffId},
                {"@TicketStatusId", ticket.TicketStatusId},
                {"@Number", ticket.Number},
                {"@Subject", ticket.Subject},
                {"@ServicePassword", ticket.ServicePassword},
                {"@Content", ticket.Content},
                {"@Attachment", ticket.Attachment},
                {"@ParentId", ticket.ParentId},
                {"@ParentType", ticket.ParentType},
                {"@id", ticket.Id},
            };
            String sql = "update TICKETS set SVT_ID = @ServiceTypeId, DEP_ID = @DepartmentId, OLN_ID = @OrderLineId, TKC_ID = @TicketCategoryId, STF_ID = @StaffId, TKS_ID = @TicketStatusId, TKT_NUMBER = @Number, TKT_SUBJECT = @Subject, " +
                "TKT_SERVICE_PASSWORD = @ServicePassword, TKT_CONTENT = @Content, TKT_ATTACHMENT = @Attachment, PARENT_ID = @ParentId, PARENT_TYPE = @ParentType where TKT_ID = @id";
            return DB.exec(sql, param);
        }

        public bool delete(int id)
        {
            object[,] param = {
                {"@id", id}   
            };
            String sql = "delete from TICKETS where TKT_ID = @id";
            return DB.exec(sql, param);
        }

        public bool delete(String ids)
        {
            object[,] param = {
                {"@ids", ids}   
            };
            String sql = "delete from TICKETS where TKT_ID in (" + ids + ")";
            return DB.exec(sql);
        }

        public DataTable get(int id)
        {
            object[,] param = {
                {"@id", id}   
            };
            String sql = "select * from TICKETS where TKT_ID = @id";
            return DB.getData(sql, param);
        }

        public DataTable getAll(string orderBy)
        {
            String sql = "select distinct TKT.*, TKC.TKC_NAME, DEP.DEP_NAME, TKS.TKS_NAME, OLN.OLN_DESC from TICKETS TKT left join TICKET_COMMENTS TCM on (TKT.TKT_ID = TCM.TKT_ID) " +
                "join TICKET_CATEGORIES TKC on (TKT.TKC_ID = TKC.TKC_ID) " +
                "join DEPARTMENTS DEP on (TKT.DEP_ID = DEP.DEP_ID) " +
                "join TICKET_STATUSES TKS on (TKT.TKS_ID = TKS.TKS_ID) " +
                "join ORDER_LINES OLN on (TKT.OLN_ID = OLN.OLN_ID) ";
            sql += orderBy;
            return DB.getData(sql);
        }

        public DataTable getByStatus(int statusId, string orderBy)
        {
            object[,] param = { };
            String sql = "select distinct TKT.*, TKC.TKC_NAME, DEP.DEP_NAME, TKS.TKS_NAME, OLN.OLN_DESC from TICKETS TKT left join TICKET_COMMENTS TCM on (TKT.TKT_ID = TCM.TKT_ID) " +
                "join TICKET_CATEGORIES TKC on (TKT.TKC_ID = TKC.TKC_ID) " +
                "join DEPARTMENTS DEP on (TKT.DEP_ID = DEP.DEP_ID) " +
                "join TICKET_STATUSES TKS on (TKT.TKS_ID = TKS.TKS_ID) " +
                "join ORDER_LINES OLN on (TKT.OLN_ID = OLN.OLN_ID) " +
                "where 1 = 1 ";
            if (statusId != 0) {
                sql += "and TKT.TKS_ID = @statusId ";
                DB.addParam(ref param, "@statusId", statusId);
            }
            sql += orderBy;
            return DB.getData(sql, param);
        }

        public DataTable getByStaff(int staffId, string statusIds, string orderBy)
        {
            object[,] param = { };
            String sql = "select distinct TKT.*, TKC.TKC_NAME, DEP.DEP_NAME, TKS.TKS_NAME, OLN.OLN_DESC from TICKETS TKT left join TICKET_COMMENTS TCM on (TKT.TKT_ID = TCM.TKT_ID) " +
                "join TICKET_CATEGORIES TKC on (TKT.TKC_ID = TKC.TKC_ID) " +
                "join DEPARTMENTS DEP on (TKT.DEP_ID = DEP.DEP_ID) " +
                "join TICKET_STATUSES TKS on (TKT.TKS_ID = TKS.TKS_ID) " +
                "join ORDER_LINES OLN on (TKT.OLN_ID = OLN.OLN_ID) " +
                "where 1 = 1 ";
            if (staffId != 0)
            {
                sql += "and TKT.STF_ID = @StaffId ";
                DB.addParam(ref param, "@StaffId", staffId);
            }

            if (statusIds != "")
            {
                sql += "and TKT.TKS_ID in (" + statusIds + ") ";
            }

            sql += orderBy;
            return DB.getData(sql, param);
        }

        public object getMaxNumber()
        {
            String sql = "select max(TKT_NUMBER) from TICKETS";
            return DB.getValue(sql);
        }

        public DataTable filter(string keyword, string statuses, string searchIn, bool in2Months, string orderBy)
        {
            String sql = "select distinct TKT.*, TKC.TKC_NAME, DEP.DEP_NAME, TKS.TKS_NAME, OLN.OLN_DESC from TICKETS TKT left join TICKET_COMMENTS TCM on (TKT.TKT_ID = TCM.TKT_ID) " +
                "join TICKET_CATEGORIES TKC on (TKT.TKC_ID = TKC.TKC_ID) " +
                "join DEPARTMENTS DEP on (TKT.DEP_ID = DEP.DEP_ID) " +
                "join TICKET_STATUSES TKS on (TKT.TKS_ID = TKS.TKS_ID) " +
                "join ORDER_LINES OLN on (TKT.OLN_ID = OLN.OLN_ID) " +
                "where 1 = 1 ";
            object[,] param = {};

            if(keyword != "") {
                if (searchIn == "TICKETS")
                {
                    sql += "and (lower(TKT.TKT_SUBJECT) like @Subject or lower(TKT.TKT_CONTENT) like @Content) ";
                    DB.addParam(ref param, "@Subject", "%" + keyword + "%");
                    DB.addParam(ref param, "@Content", "%" + keyword + "%");
                }
                else
                {
                    sql += "and lower(TCM.TKT_CONTENT) like @Content ";
                    DB.addParam(ref param, "@Content", "%" + keyword + "%");
                }
            }

            if (statuses != "")
            {
                sql += "and TKT.TKS_ID in ("+ statuses +") ";
                DB.addParam(ref param, "@Statuses", statuses);
            }

            if (in2Months == true)
            {
                sql += "and DATEDIFF(m, TKT.TKT_TIMESTAMP, GETDATE()) <= 2 ";
            }

            sql += orderBy;

            DataTable dt = DB.getData(sql, param);
            
            return dt;
        }
    }
}
